package com.etooling.germa.service.Impl;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import com.etooling.framework.svo.ReplyObj;
import com.etooling.framework.utils.WebUtils;
import com.etooling.germa.entity.ImportBomDetails;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.etooling.framework.entity.AttachFile;
import com.etooling.framework.entity.ImportDataBatch;
import com.etooling.framework.repository.ImportDataBatchRepository;
import com.etooling.framework.utils.DBUtils;
import com.etooling.germa.repository.ImportBomDetailsRepository;
import com.etooling.germa.service.ImportBomService;

import cn.hutool.core.lang.UUID;
import cn.hutool.core.util.ObjectUtil;
import lombok.AllArgsConstructor;
import lombok.Getter;

@Service
public class ImportBomServiceImpl implements ImportBomService {

	@Autowired
	private ImportBomDetailsRepository importBomDetailsRepository;

	@Autowired
	private ImportDataBatchRepository importDataBatchRepository;

	private DBUtils util = new DBUtils();

	@PersistenceContext
	private EntityManager entityManager;

	private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	@Getter
	@AllArgsConstructor
	private static class Model {
		private Integer col;
		private String name, dataType;
		private boolean canNull;
	}

	private static final List<Model> COLUMN_DEF_LIST = Arrays
			.asList(new Model[] {
					new Model(0, "物料层级", "Integer", false),
					new Model(1, "型号名称", "String", false),
					new Model(2, "型号编码", "String", false),
					new Model(3, "规格名称", "String", false),
					new Model(4, "规格编码", "String", false),
					new Model(5, "单位", "String", false),
					new Model(6, "数量", "Double", false),
					new Model(7, "产品角色", "String", false),
					new Model(8, "备注信息", "String", true) });

	private Object getCellValue(Cell cell, Model column) {
		if (cell != null) {
			String value = null;
			switch (cell.getCellType()) {
			case STRING: {
				value = cell.getStringCellValue();
				break;
			}
			case NUMERIC: {
				if (DateUtil.isCellDateFormatted(cell)) {
					value = sdf.format(cell.getDateCellValue());
				} else {
					value = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			case BOOLEAN: {
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			}
			case FORMULA:
				value = cell.getCellFormula();
				break;
			default:
				break;
			}
			if (StringUtils.isNotBlank(value)) {
				if ("Integer".equals(column.getDataType())) {
					return Double.valueOf(value).intValue();
				} else if ("Double".equals(column.getDataType())) {
					return Double.valueOf(value);
				}
				return value;
			}
		}
		return null;
	}

	// 检查表头行是否为空的实现
	private boolean isHeaderRowEmpty(Row row) {
		for (Model column : COLUMN_DEF_LIST) {
			Cell cell = row.getCell(column.getCol());
			if (cell == null || cell.getCellType() == CellType.BLANK) {
				return true;
			}
		}
		return false;
	}

	@Override
	public ReplyObj processUpload(ImportDataBatch importDataBatch, String loginId, String companyId, String bomName, String bomMemo,String specsId) {
		AttachFile attachFile = importDataBatch.getAttachFile();
		long startTime = System.currentTimeMillis();
		try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
			HttpGet httpGet = new HttpGet(attachFile.getWebPath());
			try (CloseableHttpResponse response = httpClient.execute(httpGet)) {
				if (response.getStatusLine().getStatusCode() == 200) {
					try (InputStream input = response.getEntity().getContent()) {
						Workbook wb = null;
						if (attachFile.getFileType().toLowerCase().equals(".xlsx")) {
							wb = new XSSFWorkbook(input);
						} else if (attachFile.getFileType().toLowerCase().equals(".xls")) {
							wb = new HSSFWorkbook(input);
						}
						if (WebUtils.checkStr(wb)) {
							Sheet sheet = wb.getSheetAt(0);
							// 检查是否为空文件
//							Row headerRow = sheet.getRow(0);
//							if (isHeaderRowEmpty(headerRow)) {
//								importDataBatch.setStatus(ImportDataBatch.STATUS_UPLOAD_FAILE);
//								importDataBatch.setErrorMemo("您上传的文件为空");
//								importDataBatchRepository.save(importDataBatch);
//								return WebUtils.flushSuccess(attachFile);
//							}
							Session session = entityManager.unwrap(Session.class);
							session.doWork(new Work() {
								@Override
								public void execute(Connection connection) throws SQLException {
									connection.setAutoCommit(false);
									String sql = "insert into import_data.t_import_bom_details (id, level, material_goods_name, material_goods_code, material_specs_name, material_specs_code, unit, unit_num, material_tag, memo, seq, status, imp_data_bach_id, result_memo) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
									PreparedStatement pstmt = null;
									Integer lastValue=0;

									try {
										pstmt = connection.prepareStatement(sql);
										Integer seq = 1;
										Integer nowLevel = -1;
										boolean hasZeroLevel = false; // 用于检查是否存在0级层级
										for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
											Row row = sheet.getRow(i);

											Cell cell = row.getCell(0);
//
//											Integer numericCellValue ;

											List<Object> valueList = new ArrayList<Object>();
											valueList.add(UUID.fastUUID().toString().replace("-", ""));
											List<String> checkList = new ArrayList<String>();
											for (Model column : COLUMN_DEF_LIST) {
//												Object o = getCellValue(cell, column);
//												String string = o.toString();
//												String[] split = string.split("\\.");
//												int anInt = 0;
//												if (split.length > 0){
//													anInt = Integer.parseInt(split[0]);
//												}else {
//													anInt = Integer.parseInt(string);
//												}
//												numericCellValue = anInt ;
//												if ("物料层级".equals(column.getName())) {
//													if (numericCellValue == 0) {
//														if (hasZeroLevel) {
//															checkList.add("存在0层级");
//														} else {
//															hasZeroLevel = true;
//														}
//													} else {
//														// 检查上下层级是否符合要求
//														if (numericCellValue-lastValue<=1) {
//															//正确
//
//														} else {
//															//不正确的层级
//															checkList.add("层级跨越");
//														}
//													}
//												}

												Object value = getCellValue(row.getCell(column.getCol()), column);
												valueList.add(value);
												if (ObjectUtil.isNull(value) && !column.isCanNull()) {
													checkList.add(column.getName() + "不能为空");
												}
												 //数量要大于0
												if ("数量".equals(column.getName())) {
													Double unitNum = (Double) value;
													if (unitNum == null || unitNum <= 0) {
														checkList.add(" 数量必须大于0 ");
													}
												}
//												lastValue = numericCellValue;
											}

											valueList.add(seq);
											valueList.add("new");
											valueList.add(importDataBatch.getId());
											valueList.add(String.join(";", checkList));

											util.setPstmt(pstmt, valueList);
											seq++;
											pstmt.addBatch();
											if (seq % 1000 == 0) {
												pstmt.executeBatch();
												pstmt.clearBatch();
											}
										}
										pstmt.executeBatch();
									} catch (Exception e) {
										e.printStackTrace();
										connection.rollback();
									} finally {
										connection.commit();
										pstmt.close();
									}
								}
							});
							wb.close();
						}
					}
				}
			}
		} catch (
		IOException e) {
			System.err.println("File download failed: " + e.getMessage());

		}
		System.out.println("execute At >> "+(System.currentTimeMillis()-startTime)+"ms");
		importDataBatch.setStatus(ImportDataBatch.STATUS_UPLOAD_COMPLETED);
//		String importDataBatchId = importDataBatch.getId();
//		importBomDetailsRepository.setGoodsSpecsId(importDataBatchId,specsId);

		return WebUtils.flushSuccess(attachFile);
	}

}
//		InputStream inputStream = null;
//		try {
//			AttachFile attachFile = importDataBatch.getAttachFile();
//			String webPath = attachFile.getWebPath();
//			Employee employee = new Employee();
//			employee.setId(loginId);
//			// 流处理打开当时打开的文件
//			inputStream = downloadExcel(webPath);
//
//			// 创建 DynamicAnalysisEventListener 实例, 进行读取Excel内容
//			EasyExcelEventListener listener = new EasyExcelEventListener();
//
//			// 读取 Excel 文件并处理数据 // headRowNumber 标题行占几行，默认1行
//			EasyExcel.read(inputStream, listener).sheet().headRowNumber(0).doRead();
//
//			// 获取处理后的数据
//			List<Map<Integer, String>> dataList = listener.getDataList();
//
//			// 校验文件内容是否为空或只有标题行
//			if (dataList.size() <= 1) {
//				importDataBatch.setDoneTime(new Date());
//				importDataBatch.setErrorMemo("导入的文件内容为空或只有标题行，请提供有效数据");
//				importDataBatch.setStatus(ImportDataBatch.STATUS_SUBMIT_FAILE);
//				importDataBatchRepository.save(importDataBatch);
//				return;
//			}
//
//			// 解析第一行的标题内容
//			Map<Integer, String> titleMap = dataList.get(0);
//			// 反转获得标题在Excel表中的列序号
//			Map<String, Integer> reversedMap = new HashMap<>();
//			for (Map.Entry<Integer, String> entry : titleMap.entrySet()) {
//				reversedMap.put(entry.getValue(), entry.getKey());
//			}
//			// 作为 产品数据导入 ，必须要有的列数据内容
//			List<String> canNotNullTitleList = new ArrayList<>();
//			canNotNullTitleList.add("物料层级");
//			canNotNullTitleList.add("规格编码");
//			canNotNullTitleList.add("单位");
//			canNotNullTitleList.add("数量");
//			canNotNullTitleList.add("产品角色");
//			Set<String> strings = reversedMap.keySet();
//			// 检查 canNotNullTitleList 中的所有元素是否都在 strings 中
//			boolean containsAll = strings.containsAll(canNotNullTitleList);
//			if (!containsAll) {
//				// 找出缺少的元素并输出
//				StringBuilder stringBuilder = new StringBuilder();
//				for (String element : canNotNullTitleList) {
//					if (!strings.contains(element)) {
//						stringBuilder.append(element);
//						stringBuilder.append(";");
//					}
//				}
//				importDataBatch.setDoneTime(new Date());
//				importDataBatch.setErrorMemo(
//						"导入的文件数据缺失，导入产品数据必须包括的内容：物料层级，规格编码，单位，数量，产品角色；当前导入的文件缺少：" + stringBuilder.toString());
//				importDataBatch.setStatus(ImportDataBatch.STATUS_SUBMIT_FAILE);
//				importDataBatchRepository.save(importDataBatch);
//				return;
//			}
//
//			// 获取对应标题的的列序号, 没有这个标题的序号就是-1, 在数据列表里面的内容 -1 序号的是没有值的
//			Integer levelIndex = reversedMap.containsKey("物料层级") ? reversedMap.get("物料层级") : -1;
//			Integer skuCodeIndex = reversedMap.containsKey("规格编码") ? reversedMap.get("规格编码") : -1;
//			Integer unitIndex = reversedMap.containsKey("单位") ? reversedMap.get("单位") : -1;
//			Integer numIndex = reversedMap.containsKey("数量") ? reversedMap.get("数量") : -1;
//			Integer roleIndex = reversedMap.containsKey("产品角色") ? reversedMap.get("产品角色") : -1;
//			Integer memoIndex = reversedMap.containsKey("备注信息") ? reversedMap.get("备注信息") : -1;
//
//			// 大于等于0的整数
//			Pattern pattern1 = Pattern.compile("^\\d+$");
//			// 数字
//			Pattern pattern2 = Pattern.compile("-?\\d+(\\.\\d+)?");
//
//			// 记录所有skuCode
//			Map<String, Integer> skuCodeIndexMap = new HashMap<>();
//			// 错误结果map
//			Map<Integer, StringBuilder> failResultMap = new TreeMap<>();
//
//			Map<Integer, String> firstRow = dataList.get(1);
//			if (!firstRow.containsKey(levelIndex) || !"0".equals(firstRow.get(levelIndex))) {
//				importDataBatch.setDoneTime(new Date());
//				importDataBatch.setErrorMemo("不是从物料层级0开始，Bom表结构不合理");
//				importDataBatch.setStatus(ImportDataBatch.STATUS_SUBMIT_FAILE);
//				importDataBatchRepository.save(importDataBatch);
//				return;
//			}
//
//			List<ImportBomDetails> detailsList = new ArrayList<>();
//			boolean haveZeroLevel = false;
//			int rowLevel = 0;
//
//			// 循环处理数据, 从第二行开始, 第一行是标题
//			for (int i = 1; i < dataList.size(); i++) {
//				Map<Integer, String> map = dataList.get(i);
//				// 获取对应标题的所有内容
//				String level = map.get(levelIndex);
//				String skuCode = map.get(skuCodeIndex);
//				String unit = map.get(unitIndex);
//				String num = map.get(numIndex);
//				String role = map.get(roleIndex);
//				String memo = map.get(memoIndex);
//
//				StringBuilder sb;
//				if (!failResultMap.containsKey(i)) {
//					sb = new StringBuilder();
//				} else {
//					sb = failResultMap.get(i);
//				}
//
//				if (!WebUtils.checkStr(level)) {
//					sb.append("物料层级是空的").append("\n");
//				} else {
//					if (!pattern1.matcher(level).matches()) {
//						sb.append("物料层级不是大于等于0的整数").append("\n");
//					}
//				}
//
//				int levelInt = Integer.parseInt(level);
//				if (levelInt == 0) {
//					if (!haveZeroLevel) {
//						haveZeroLevel = true;
//					} else {
//						sb.append("已经有了物料层级0").append("\n");
//					}
//				}
//				// 层级的跳动超过2，例如，某个产品是层级2，那么它的下属Bom表应该是层级3，但是结果是层级4，那就是错的
//				if ((levelInt - rowLevel) >= 2) {
//					sb.append("层级有问题，与上一行的层级有断层").append("\n");
//				} else {
//					rowLevel = levelInt;
//				}
//
//				if (!WebUtils.checkStr(skuCode)) {
//					sb.append("规格编码是空的").append("\n");
//				} else {
//					skuCodeIndexMap.put(skuCode, i);
//				}
//				if (!WebUtils.checkStr(unit)) {
//					sb.append("单位是空的").append("\n");
//				}
//				if (!WebUtils.checkStr(num)) {
//					sb.append("数量是空的").append("\n");
//				} else {
//					if (!pattern2.matcher(num).matches()) {
//						sb.append("数量不是数字").append("\n");
//					}
//				}
//				if (!WebUtils.checkStr(role)) {
//					sb.append("产品角色是空的").append("\n");
//				}
//
//				if (sb.length() == 0) {
//					ImportBomDetails details = new ImportBomDetails();
//					details.setImportDataBatch(importDataBatch);
//					details.setLevel(levelInt);
//					details.setMaterialGoodsName(map.get(1));
//					details.setMaterialGoodsCode(map.get(2));
//					details.setMaterialSpecsName(map.get(3));
//					details.setMaterialSpecsCode(map.get(4));
//					details.setUnit(unit);
//					details.setUnitNum(Double.parseDouble(num));
//					details.setMaterialTag(map.get(7));
//					details.setMemo(memo);
//					details.setStatus("new");
//					detailsList.add(details);
//				} else {
//					failResultMap.put(i, sb);
//				}
//			}
//			importDataBatch.setDoneTime(new Date());
//			if (failResultMap.isEmpty()) {
//				importBomDetailsRepository.saveAll(detailsList);
//				importDataBatch.setStatus(ImportDataBatch.STATUS_UPLOAD_COMPLETED);
//			} else {
//				StringBuilder errorMemo = new StringBuilder();
//				for (Map.Entry<Integer, StringBuilder> entry : failResultMap.entrySet()) {
//					errorMemo.append("第").append(entry.getKey()).append("行错误: ").append(entry.getValue().toString())
//							.append("\n");
//				}
//				importDataBatch.setErrorMemo(errorMemo.toString());
//				importDataBatch.setStatus(ImportDataBatch.STATUS_SUBMIT_FAILE);
//			}
//			importDataBatchRepository.save(importDataBatch);
//			// List<ImportBomDetails> detailsList = new ArrayList<>();
////            for (int i = 0; i < dataList.size(); i++) {
////                Map<Integer, String> integerStringMap = dataList.get(i);
////                ImportBomDetails importBomDetails = new ImportBomDetails();
//////                Integer level = Integer.valueOf(integerStringMap.get(0));
////                String materialGoodsName = integerStringMap.get(1);
////                String materialGoodsCode = integerStringMap.get(2);
////                String materialSpecsName = integerStringMap.get(3);
////                String materialSpecsCode = integerStringMap.get(4);
////                String unit = integerStringMap.get(5);
//////                Double unitNum = Double.valueOf(integerStringMap.get(6));
////                String materialTag = integerStringMap.get(7);
////                String memo = integerStringMap.get(8);
//////                importBomDetails.setLevel(level);
////                importBomDetails.setMaterialGoodsName(materialGoodsName);
////                importBomDetails.setMaterialGoodsCode(materialGoodsCode);
////                importBomDetails.setMaterialSpecsName(materialSpecsName);
////                importBomDetails.setMaterialSpecsCode(materialSpecsCode);
////                importBomDetails.setUnit(unit);
//////                importBomDetails.setUnitNum(unitNum);
////                importBomDetails.setMaterialTag(materialTag);
////                importBomDetails.setMemo(memo);
////                importBomDetails.setStatus("new");
////                detailsList.add(importBomDetails);
////            }
//
//		} catch (Exception e) {
//			e.printStackTrace();
//			importDataBatch.setDoneTime(new Date());
//			importDataBatch.setErrorMemo("读取文件内容发生异常，请重新上传文件");
//			importDataBatch.setStatus(ImportDataBatch.STATUS_SUBMIT_FAILE);
//			importDataBatchRepository.save(importDataBatch);
////            e.printStackTrace();
////            // 获取异常类型
////            String exceptionType = e.getClass().getSimpleName();
////            // 获取异常消息
////            String exceptionMessage = e.getMessage();
////            // 获取异常的位置（代码行号）
////            StackTraceElement[] stackTrace = e.getStackTrace();
////            String exceptionLocation = "";
////            if (stackTrace.length > 0) {
////                StackTraceElement firstStackTraceElement = stackTrace[0];
////                exceptionLocation = firstStackTraceElement.getClassName() + "." + firstStackTraceElement.getMethodName() + "(" + firstStackTraceElement.getFileName() + ":" + firstStackTraceElement.getLineNumber() + ")";
////            }
////
////            // 组合成字符串
////            String exceptionDetails = "异常类型: " + exceptionType + "\n" + "异常发生位置: " + exceptionLocation + "\n" + "异常内容: " + exceptionMessage;
////
////            // 打印异常信息字符串
////            System.out.println(exceptionDetails);
////            // 修改上传文件记录
////            importDataBatch.setDoneTime(new Date());
////            importDataBatch.setErrorMemo("提交过程中发生异常，请联系管理员进行处理");
////            importDataBatch.setStatus(ImportDataBatch.STATUS_SUBMIT_FAILE);
////            importDataBatchRepository.save(importDataBatch);
//
//		} finally {
//			try {
//				// 关闭流
//				if (inputStream != null) {
//					inputStream.close();
//				}
//			} catch (IOException e) {
//				e.printStackTrace();
//			}
//		}

